SELECT * FROM datediff_big_vu_prepare_v1
GO

SELECT * FROM datediff_big_vu_prepare_v2
GO

SELECT * FROM datediff_big_vu_prepare_v3
GO

SELECT * FROM datediff_big_vu_prepare_v4
GO

SELECT * FROM datediff_big_vu_prepare_v5
GO

SELECT * FROM datediff_big_vu_prepare_v6
GO

SELECT * FROM datediff_big_vu_prepare_v7
GO

SELECT * FROM datediff_big_vu_prepare_v8
GO

SELECT * FROM datediff_big_vu_prepare_v9
GO

SELECT * FROM datediff_big_vu_prepare_v10
GO

SELECT * FROM datediff_big_vu_prepare_v11
GO

SELECT * FROM datediff_big_vu_prepare_v13
GO

EXEC datediff_big_vu_prepare_p1
GO

EXEC datediff_big_vu_prepare_p2
GO

EXEC datediff_big_vu_prepare_p3
GO

EXEC datediff_big_vu_prepare_p4
GO

EXEC datediff_big_vu_prepare_p5
GO

EXEC datediff_big_vu_prepare_p6
GO

EXEC datediff_big_vu_prepare_p7
GO

EXEC datediff_big_vu_prepare_p8
GO

EXEC datediff_big_vu_prepare_p9
GO

EXEC datediff_big_vu_prepare_p10
GO

EXEC datediff_big_vu_prepare_p11
GO

EXEC datediff_big_vu_prepare_p12
GO

EXEC datediff_big_vu_prepare_p13
GO

begin transaction
go

SELECT DATEDIFF_BIG(fakeoption, cast('2023-01-01 01:01:20.98' as datetime), cast('2023-01-01 01:01:20.98' as datetime))
go

if (@@trancount > 0) select cast('compile time error' as text) else select cast('runtime error' as text)
GO

if (@@trancount > 0) rollback tran
GO

begin transaction
go

SELECT DATEDIFF_BIG(nanosecond, cast('1900-01-01 01:01:20.98' as datetime), cast('3000-01-01 01:01:20.98' as datetime))
go

if (@@trancount > 0) select cast('compile time error' as text) else select cast('runtime error' as text)
GO

if (@@trancount > 0) rollback tran
GO

begin transaction
go

SELECT DATEADD(YY,-30000000, cast('1900-01-01' as datetime));
go

if (@@trancount > 0) select cast('compile time error' as text) else select cast('runtime error' as text)
GO

if (@@trancount > 0) rollback tran
GO

Declare @runForDate datetime = '01/22/2012';
Declare @evaluationDate datetime = DATEADD(DAY,-1,@runForDate);
Select DATEDIFF_BIG(wk,7,@evaluationDate)
GO

-- Basic Functionality
SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATE), CAST('2023-01-01' AS DATE)) AS SameDate;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATETIME), CAST('2023-01-02' AS DATETIME)) AS ConsecutiveDaysSameWeek;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATE), CAST('2022-12-25' AS DATE)) AS DifferentWeeks;
GO

SELECT DATEDIFF_BIG(week, CAST('2010-01-01' AS DATE), CAST('2009-12-19' AS DATE)) AS DifferentWeeks;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATE), CAST('2023-01-31' AS DATE)) AS SameQuarter;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATETIME), CAST('2023-02-01' AS DATETIME)) AS ConsecutiveMonths;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-02-01' AS DATETIME), CAST('2023-01-01' AS DATETIME)) AS ConsecutiveMonths;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATE), CAST('2023-07-25' AS DATE)) AS DifferentQuarters;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2010-01-01' AS DATE), CAST('2011-12-19' AS DATE)) AS DifferentQuarters;
GO

-- No boundary crossed
SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATE), CAST('2023-01-07' AS DATE)) AS NoBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-07' AS DATE), CAST('2023-01-01' AS DATE)) AS NoBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATE), CAST('2023-03-31' AS DATE)) AS NoBoundaryCrossed;
GO

-- One boundary crossed
SELECT DATEDIFF_BIG(week, CAST('2023-01-07' AS DATETIME), CAST('2023-01-08' AS DATETIME)) AS OneBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(wk, CAST('2023-01-07' AS DATETIME), CAST('2023-01-08' AS DATETIME)) AS OneBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-08' AS DATETIME), CAST('2023-01-07' AS DATETIME)) AS OneBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATETIME), CAST('2023-04-01' AS DATETIME)) AS OneBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-04-01' AS DATETIME), CAST('2023-01-01' AS DATETIME)) AS OneBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(qq, CAST('2023-01-01' AS DATETIME), CAST('2022-12-31' AS DATETIME)) AS OneBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2022-12-31' AS DATETIME), CAST('2023-01-01' AS DATETIME)) AS OneBoundaryCrossed;
GO

-- Multiple boundaries crossed
SELECT DATEDIFF_BIG(week, CAST('2023-01-07' AS DATE), CAST('2023-01-22' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(ww, CAST('2023-01-07' AS DATE), CAST('2023-01-22' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-22' AS DATE), CAST('2023-01-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-07' AS DATE), CAST('2023-01-22' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(q, CAST('2023-01-07' AS DATE), CAST('2023-01-22' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-22' AS DATE), CAST('2023-01-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-05-22' AS DATE), CAST('2023-06-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-06-22' AS DATE), CAST('2023-05-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-06-22' AS DATE), CAST('2023-07-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-07-22' AS DATE), CAST('2023-06-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-22' AS DATE), CAST('2024-01-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2024-01-22' AS DATE), CAST('2023-01-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-22' AS DATE), CAST('2024-04-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2024-01-22' AS DATE), CAST('2023-04-07' AS DATE)) AS MultipleBoundariesCrossed;
GO

-- Time-sensitive crossing
SELECT DATEDIFF_BIG(week, CAST('2023-01-07 23:59' AS DATETIME), CAST('2023-01-08 00:00' AS DATETIME)) AS TimeSensitiveBoundary;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-08 00:00' AS DATETIME), CAST('2023-01-07 23:59' AS DATETIME)) AS TimeSensitiveBoundary;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-09-30 23:59' AS DATETIME), CAST('2023-10-01 00:00' AS DATETIME)) AS TimeSensitiveBoundary;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-10-01 00:00' AS DATETIME), CAST('2023-09-30 23:59' AS DATETIME)) AS TimeSensitiveBoundary;
GO

-- Overlapping across years
SELECT DATEDIFF_BIG(week, CAST('2022-12-31' AS DATE), CAST('2023-01-08' AS DATE)) AS YearBoundaryCrossed;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2022-12-31' AS DATE), CAST('2023-01-08' AS DATE)) AS YearBoundaryCrossed;
GO

-- Week starts on Sunday
SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATE), CAST('2023-01-08' AS DATE)) AS WeekStartsOnSunday;
GO

-- Week ends on Saturday
SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATETIME), CAST('2023-01-14' AS DATETIME)) AS WeekEndsOnSaturday;
GO

-- Multiple weeks with no overlap
SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATE), CAST('2023-01-28' AS DATE)) AS MultipleWeeksNoOverlap;
GO

-- Start and end at exact boundaries
SELECT DATEDIFF_BIG(week, CAST('2023-01-01 00:00:00' AS DATETIME), CAST('2023-01-08 00:00:00' AS DATETIME)) AS ExactBoundary;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01 00:00:00' AS DATETIME), CAST('2023-04-01 00:00:00' AS DATETIME)) AS ExactBoundary;
GO

-- Start and end with fractional seconds
SELECT DATEDIFF_BIG(week, CAST('2023-01-01 23:59:59.999999' AS DATETIME2), CAST('2023-01-08 00:00:00.000000' AS DATETIME2)) AS FractionalSecondsPrecision;
GO

-- Leap Year
SELECT DATEDIFF_BIG(week, CAST('2020-02-28' AS DATETIME), CAST('2020-03-01' AS DATETIME)) AS AcrossLeapDay;
GO

SELECT DATEDIFF_BIG(week, CAST('2020-02-29' AS DATE), CAST('2020-03-07' AS DATE)) AS WeekWithLeapDay;
GO

-- Negative Intervals
SELECT DATEDIFF_BIG(week, CAST('2023-01-08' AS DATE), CAST('2023-01-01' AS DATE)) AS NegativeInterval;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-08 12:00' AS DATETIME), CAST('2023-01-01 12:00' AS DATETIME)) AS NegativeWithTime;
GO

SELECT DATEDIFF_BIG(week, CAST('9999-01-01' AS DATE), CAST('0001-01-01' AS DATE)) AS Date_ReverseRange;
GO

SELECT DATEDIFF_BIG(week, CAST('9999-01-01' AS DATETIME2), CAST('0001-01-01' AS DATETIME2)) AS Datetime2_ReverseRange;
GO

-- Boundary Conditions
SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATETIME), CAST('2023-01-07' AS DATETIME)) AS SameWeekBoundary;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-07' AS DATE), CAST('2023-01-08' AS DATE)) AS WeekResetBoundary;
GO

-- Across Years
SELECT DATEDIFF_BIG(week, CAST('2022-12-31' AS DATETIME), CAST('2023-01-01' AS DATETIME)) AS SameWeekAcrossYears;
GO

SELECT DATEDIFF_BIG(week, CAST('2022-12-31' AS DATE), CAST('2023-01-08' AS DATE)) AS DifferentWeeksAcrossYears;
GO

-- Null Inputs
SELECT DATEDIFF_BIG(week, NULL, CAST('2023-01-01' AS DATE)) AS NullStartDate;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATETIME), NULL) AS NullEndDate;
GO

SELECT DATEDIFF_BIG(week, NULL, NULL) AS BothNull;
GO

SELECT DATEDIFF_BIG(quarter, NULL, CAST('2023-01-01' AS DATE)) AS NullStartDate;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATETIME), NULL) AS NullEndDate;
GO

SELECT DATEDIFF_BIG(quarter, NULL, NULL) AS BothNull;
GO

-- Invalid Inputs
SELECT DATEDIFF_BIG(week, CAST('InvalidDate' AS DATE), CAST('2023-01-01' AS DATE)) AS InvalidStartDate;
GO

SELECT DATEDIFF_BIG(week, CAST('2023-01-01' AS DATETIME), CAST('InvalidDate' AS DATETIME)) AS InvalidEndDate;
GO

SELECT DATEDIFF_BIG(quarter, CAST('InvalidDate' AS DATE), CAST('2023-01-01' AS DATE)) AS InvalidStartDate;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2023-01-01' AS DATETIME), CAST('InvalidDate' AS DATETIME)) AS InvalidEndDate;
GO

-- Large Date Ranges
SELECT DATEDIFF_BIG(week, CAST('1923-01-01' AS DATE), CAST('2023-01-01' AS DATE)) AS CenturyDifference;
GO

SELECT DATEDIFF_BIG(week, CAST('0001-01-01' AS DATETIME), CAST('2023-01-01' AS DATETIME)) AS MillenniaDifference;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1923-01-01' AS DATE), CAST('2023-01-01' AS DATE)) AS CenturyDifference;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01' AS DATETIME), CAST('2023-01-01' AS DATETIME)) AS MillenniaDifference;
GO

-- Boundary Testing
SELECT DATEDIFF_BIG(week, CAST('0001-01-01' AS DATE), CAST('9999-12-31' AS DATE)) AS Date_FullRange;
GO

SELECT DATEDIFF_BIG(week, CAST('1753-01-01' AS DATETIME), CAST('9999-12-31' AS DATETIME)) AS Datetime_FullRange;
GO

SELECT DATEDIFF_BIG(week, CAST('0001-01-01' AS DATETIME), CAST('9999-12-31' AS DATETIME)) AS Datetime_FullRange_Error;
GO

SELECT DATEDIFF_BIG(week, CAST('0001-01-01 00:00:00.000000' AS DATETIME2), CAST('9999-12-31 23:59:59.999999' AS DATETIME2)) AS Datetime2_FullRange;
GO

SELECT DATEDIFF_BIG(week, CAST('1900-01-01 00:00:00' AS SMALLDATETIME), CAST('2079-06-06 23:59:59' AS SMALLDATETIME)) AS Smalldatetime_FullRange;
GO

SELECT DATEDIFF_BIG(week, CAST('1753-01-01 00:00:00' AS SMALLDATETIME), CAST('2079-06-06 23:59:59' AS SMALLDATETIME)) AS Smalldatetime_FullRange_Error;
GO

SELECT DATEDIFF_BIG(week, CAST('1900-01-01 00:00:00' AS SMALLDATETIME), CAST('9999-12-31 23:59:59' AS SMALLDATETIME)) AS Smalldatetime_FullRange_Error;
GO

SELECT DATEDIFF_BIG(week, CAST('0001-01-01 00:00:00.000000 +00:00' AS DATETIMEOFFSET), CAST('9999-12-31 23:59:59.999999 +00:00' AS DATETIMEOFFSET)) AS DatetimeOffset_FullRange;
GO

SELECT DATEDIFF_BIG(week, CAST('12:00:00' AS TIME), CAST('18:00:00' AS TIME)) AS TimeDiff;
GO

SELECT DATEDIFF_BIG(week, CAST('12:00:00' AS TIME), CAST('2024-01-01' AS DATE)) AS TimeToDateDiff;
GO

SELECT DATEDIFF_BIG(week, CAST('2024-01-01' AS DATE), CAST('12:00:00' AS TIME)) AS DateToTimeDiff;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01' AS DATE), CAST('9999-12-31' AS DATE)) AS Date_FullRange;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1753-01-01' AS DATETIME), CAST('9999-12-31' AS DATETIME)) AS Datetime_FullRange;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01' AS DATETIME), CAST('9999-12-31' AS DATETIME)) AS Datetime_FullRange_Error;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01 00:00:00.000000' AS DATETIME2), CAST('9999-12-31 23:59:59.999999' AS DATETIME2)) AS Datetime2_FullRange;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1900-01-01 00:00:00' AS SMALLDATETIME), CAST('2079-06-06 23:59:59' AS SMALLDATETIME)) AS Smalldatetime_FullRange;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1753-01-01 00:00:00' AS SMALLDATETIME), CAST('2079-06-06 23:59:59' AS SMALLDATETIME)) AS Smalldatetime_FullRange_Error;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1900-01-01 00:00:00' AS SMALLDATETIME), CAST('9999-12-31 23:59:59' AS SMALLDATETIME)) AS Smalldatetime_FullRange_Error;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01 00:00:00.000000 +00:00' AS DATETIMEOFFSET), CAST('9999-12-31 23:59:59.999999 +00:00' AS DATETIMEOFFSET)) AS DatetimeOffset_FullRange;
GO

SELECT DATEDIFF_BIG(quarter, CAST('12:00:00' AS TIME), CAST('18:00:00' AS TIME)) AS TimeDiff;
GO

SELECT DATEDIFF_BIG(quarter, CAST('12:00:00' AS TIME), CAST('2024-01-01' AS DATE)) AS TimeToDateDiff;
GO

SELECT DATEDIFF_BIG(quarter, CAST('2024-01-01' AS DATE), CAST('12:00:00' AS TIME)) AS DateToTimeDiff;
GO

-- Will be fixed under BABEL-5492
SELECT DATEDIFF_BIG(week, CAST('0001-01-01' AS DATE), CAST('9999-01-01 23:59:59' AS DATETIME)) AS DateToDatetime;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01' AS DATE), CAST('9999-01-01 23:59:59' AS DATETIME)) AS DateToDatetime;
GO

-- Mixed-Type Testing
SELECT DATEDIFF_BIG(week, CAST('1753-01-01 00:00:00' AS DATETIME), CAST('9999-01-01 23:59:59.999' AS DATETIME2)) AS DatetimeToDatetime2;
GO

SELECT DATEDIFF_BIG(week, CAST('0001-01-01 00:00:00+00:00' AS DATETIMEOFFSET), CAST('2079-06-06' AS SMALLDATETIME)) AS DatetimeOffsetToSmalldatetime;
GO

SELECT DATEDIFF_BIG(week, CAST('1753-01-01 00:00:00' AS DATETIME2), CAST('1900-01-01' AS DATE)) AS Datetime2ToDate;
GO

SELECT DATEDIFF_BIG(week, CAST('0001-01-01 00:00:00+00:00' AS DATETIMEOFFSET), CAST('1753-01-01 00:00:00' AS DATETIME2)) AS DatetimeOffsetTodatetime2;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1753-01-01 00:00:00' AS DATETIME), CAST('9999-01-01 23:59:59.999' AS DATETIME2)) AS DatetimeToDatetime2;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01 00:00:00+00:00' AS DATETIMEOFFSET), CAST('2079-06-06' AS SMALLDATETIME)) AS DatetimeOffsetToSmalldatetime;
GO

SELECT DATEDIFF_BIG(quarter, CAST('1753-01-01 00:00:00' AS DATETIME2), CAST('1900-01-01' AS DATE)) AS Datetime2ToDate;
GO

SELECT DATEDIFF_BIG(quarter, CAST('0001-01-01 00:00:00+00:00' AS DATETIMEOFFSET), CAST('1753-01-01 00:00:00' AS DATETIME2)) AS DatetimeOffsetTodatetime2;
GO

-- Test Cases for Different Time Zones in DATETIMEOFFSET
SELECT DATEDIFF_BIG(week, '2023-01-01 00:00:00+00:00', '2023-01-08 00:00:00+05:30') AS SameTimeDifferentOffsets;
GO

SELECT DATEDIFF_BIG(week, '2023-01-01 00:00:00+00:00', '2023-01-08 05:30:00+05:30') AS SameTimeDifferentOffsets;
GO

SELECT DATEDIFF_BIG(week, '2023-01-01 00:00:00-05:00', '2023-01-01 00:00:00+01:00') AS StartEarlierDifferentZone;
GO

SELECT DATEDIFF_BIG(week, '2024-01-05 23:59:59+00:00', '2024-01-06 00:00:01+05:30') AS CrossWeekBoundary;
GO

SELECT DATEDIFF_BIG(week, '2000-01-01 00:00:00+00:00', '2023-01-01 00:00:00+10:00') AS LargeIntervalDifferentOffsets;
GO

SELECT DATEDIFF_BIG(week, '2023-01-01 00:00:00+05:30', '2000-01-01 00:00:00+00:00') AS ReverseIntervalDifferentOffsets;
GO

SELECT DATEDIFF_BIG(quarter, '2023-01-01 00:00:00+00:00', '2023-04-01 00:00:00+05:30') AS SameTimeDifferentOffsets;
GO

SELECT DATEDIFF_BIG(quarter, '2023-01-01 00:00:00+00:00', '2023-04-01 05:30:00+05:30') AS SameTimeDifferentOffsets;
GO

SELECT DATEDIFF_BIG(quarter, '2023-01-01 00:00:00-05:00', '2023-01-01 00:00:00+01:00') AS StartEarlierDifferentZone;
GO

SELECT DATEDIFF_BIG(quarter, '2023-12-31 23:59:59+00:00', '2024-01-01 00:00:01+05:30') AS CrossQuarterBoundary;
GO

SELECT DATEDIFF_BIG(quarter, '2000-01-01 00:00:00+00:00', '2023-01-01 00:00:00+10:00') AS LargeIntervalDifferentOffsets;
GO

SELECT DATEDIFF_BIG(quarter, '2023-01-01 00:00:00+05:30', '2000-01-01 00:00:00+00:00') AS ReverseIntervalDifferentOffsets;
GO
